{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-06-29T07:15:03.194588Z",
     "start_time": "2025-06-29T07:15:03.192563Z"
    }
   },
   "source": "import pandas as pd",
   "outputs": [],
   "execution_count": 159
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T07:15:03.347332Z",
     "start_time": "2025-06-29T07:15:03.345235Z"
    }
   },
   "cell_type": "code",
   "source": "import numpy as np",
   "id": "d1a2364a21c06c3",
   "outputs": [],
   "execution_count": 160
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T07:15:03.498231Z",
     "start_time": "2025-06-29T07:15:03.491423Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = pd.read_table(\n",
    "    \"example.tsv\", sep=\"\\s+\"\n",
    ")\n",
    "df[\"Gene\"] = [f\"Gene{i}\" for i in np.arange(1, 1670)]\n"
   ],
   "id": "ae0fdae11074cf4f",
   "outputs": [],
   "execution_count": 161
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T07:15:03.697766Z",
     "start_time": "2025-06-29T07:15:03.639050Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = df.filter([\"Gene\", \"logFC\", \"PValue\"])\n",
    "df.to_excel(\"example.xlsx\", header=True, index=False, engine=\"openpyxl\")"
   ],
   "id": "7d687c77ecead25d",
   "outputs": [],
   "execution_count": 162
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T07:15:03.851166Z",
     "start_time": "2025-06-29T07:15:03.848868Z"
    }
   },
   "cell_type": "code",
   "source": [
    "def func(x):\n",
    "    if x[\"PValue\"] >= 0.05:\n",
    "        return \"not\"\n",
    "    if x[\"logFC\"] > 1 :\n",
    "        return \"up\"\n",
    "    if x[\"logFC\"] < -1:\n",
    "        return \"down\"\n",
    "    return \"not\"\n",
    "    "
   ],
   "id": "115aa823bad0f25e",
   "outputs": [],
   "execution_count": 163
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-28T12:06:27.335332Z",
     "start_time": "2025-06-28T12:06:27.327516Z"
    }
   },
   "cell_type": "code",
   "source": "df[\"Regular\"] = df.apply(func, axis=1)",
   "id": "7e1abc42ea99fbdb",
   "outputs": [],
   "execution_count": 144
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-28T12:06:27.711472Z",
     "start_time": "2025-06-28T12:06:27.707982Z"
    }
   },
   "cell_type": "code",
   "source": "df[\"logFC_abs\"] = df.logFC.abs()",
   "id": "9c4e2b8f7a22e6f8",
   "outputs": [],
   "execution_count": 145
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-28T12:06:28.104676Z",
     "start_time": "2025-06-28T12:06:28.102008Z"
    }
   },
   "cell_type": "code",
   "source": "",
   "id": "428d6f4e0615a43d",
   "outputs": [],
   "execution_count": 146
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "461efa17a70faf17"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-28T12:06:28.661124Z",
     "start_time": "2025-06-28T12:06:28.647056Z"
    }
   },
   "cell_type": "code",
   "source": [
    "def mark_top_5_or_all_F(group):\n",
    "    group[\"Regular\"] = group.name\n",
    "    if group.name == 'not':\n",
    "        # 分组名是 'not'，全部标记为 'F'\n",
    "        group['flag'] = 'F'\n",
    "    else:\n",
    "        # 正常分组，标记 logFC_abs 最大的5个为 'T'，其他为 'F'\n",
    "        top_5 = group.nlargest(5, 'logFC_abs')\n",
    "        group['flag'] = group['Gene'].isin(top_5['Gene']).map({True: 'T', False: 'F'})\n",
    "        print(group)\n",
    "    return group\n",
    "\n",
    "df = df.groupby('Regular',as_index=False).apply(mark_top_5_or_all_F, include_groups=False)\n",
    "\n"
   ],
   "id": "7e16c55e3ca9f3d6",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Gene     logFC        PValue  logFC_abs Regular flag\n",
      "0      Gene1 -5.444561  8.386036e-07   5.444561    down    F\n",
      "1      Gene2 -5.649062  1.463526e-06   5.649062    down    F\n",
      "2      Gene3 -5.934843  7.604120e-06   5.934843    down    F\n",
      "3      Gene4 -4.309913  8.621032e-06   4.309913    down    F\n",
      "4      Gene5 -4.166778  1.047254e-05   4.166778    down    F\n",
      "..       ...       ...           ...        ...     ...  ...\n",
      "200  Gene201 -1.234992  4.846484e-02   1.234992    down    F\n",
      "202  Gene203 -2.348426  4.866604e-02   2.348426    down    F\n",
      "203  Gene204 -1.299214  4.871354e-02   1.299214    down    F\n",
      "204  Gene205 -2.482903  4.944443e-02   2.482903    down    F\n",
      "205  Gene206 -1.352392  4.967943e-02   1.352392    down    F\n",
      "\n",
      "[142 rows x 6 columns]\n",
      "        Gene     logFC    PValue  logFC_abs Regular flag\n",
      "9     Gene10  3.172001  0.000063   3.172001      up    T\n",
      "12    Gene13  3.914482  0.000120   3.914482      up    T\n",
      "16    Gene17  4.407907  0.000195   4.407907      up    T\n",
      "20    Gene21  3.026456  0.000436   3.026456      up    T\n",
      "38    Gene39  2.585272  0.002011   2.585272      up    F\n",
      "..       ...       ...       ...        ...     ...  ...\n",
      "186  Gene187  1.775998  0.043952   1.775998      up    F\n",
      "194  Gene195  2.214651  0.046933   2.214651      up    F\n",
      "198  Gene199  1.620426  0.047815   1.620426      up    F\n",
      "199  Gene200  1.977304  0.048153   1.977304      up    F\n",
      "201  Gene202  1.365912  0.048569   1.365912      up    F\n",
      "\n",
      "[64 rows x 6 columns]\n"
     ]
    }
   ],
   "execution_count": 147
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-28T12:06:29.165032Z",
     "start_time": "2025-06-28T12:06:29.157873Z"
    }
   },
   "cell_type": "code",
   "source": "df.query(\"flag == 'T'\")",
   "id": "a35d2e598602362f",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          Gene     logFC    PValue  logFC_abs Regular flag\n",
       "0 19    Gene20 -6.926691  0.000351   6.926691    down    T\n",
       "  26    Gene27 -6.685899  0.000577   6.685899    down    T\n",
       "  48    Gene49 -6.632528  0.003559   6.632528    down    T\n",
       "  78    Gene79 -6.147763  0.008613   6.147763    down    T\n",
       "  83    Gene84 -6.051323  0.010058   6.051323    down    T\n",
       "2 9     Gene10  3.172001  0.000063   3.172001      up    T\n",
       "  12    Gene13  3.914482  0.000120   3.914482      up    T\n",
       "  16    Gene17  4.407907  0.000195   4.407907      up    T\n",
       "  20    Gene21  3.026456  0.000436   3.026456      up    T\n",
       "  133  Gene134  3.404875  0.023593   3.404875      up    T"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Gene</th>\n",
       "      <th>logFC</th>\n",
       "      <th>PValue</th>\n",
       "      <th>logFC_abs</th>\n",
       "      <th>Regular</th>\n",
       "      <th>flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">0</th>\n",
       "      <th>19</th>\n",
       "      <td>Gene20</td>\n",
       "      <td>-6.926691</td>\n",
       "      <td>0.000351</td>\n",
       "      <td>6.926691</td>\n",
       "      <td>down</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Gene27</td>\n",
       "      <td>-6.685899</td>\n",
       "      <td>0.000577</td>\n",
       "      <td>6.685899</td>\n",
       "      <td>down</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Gene49</td>\n",
       "      <td>-6.632528</td>\n",
       "      <td>0.003559</td>\n",
       "      <td>6.632528</td>\n",
       "      <td>down</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Gene79</td>\n",
       "      <td>-6.147763</td>\n",
       "      <td>0.008613</td>\n",
       "      <td>6.147763</td>\n",
       "      <td>down</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Gene84</td>\n",
       "      <td>-6.051323</td>\n",
       "      <td>0.010058</td>\n",
       "      <td>6.051323</td>\n",
       "      <td>down</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2</th>\n",
       "      <th>9</th>\n",
       "      <td>Gene10</td>\n",
       "      <td>3.172001</td>\n",
       "      <td>0.000063</td>\n",
       "      <td>3.172001</td>\n",
       "      <td>up</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Gene13</td>\n",
       "      <td>3.914482</td>\n",
       "      <td>0.000120</td>\n",
       "      <td>3.914482</td>\n",
       "      <td>up</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Gene17</td>\n",
       "      <td>4.407907</td>\n",
       "      <td>0.000195</td>\n",
       "      <td>4.407907</td>\n",
       "      <td>up</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Gene21</td>\n",
       "      <td>3.026456</td>\n",
       "      <td>0.000436</td>\n",
       "      <td>3.026456</td>\n",
       "      <td>up</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Gene134</td>\n",
       "      <td>3.404875</td>\n",
       "      <td>0.023593</td>\n",
       "      <td>3.404875</td>\n",
       "      <td>up</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 148
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-28T12:06:29.364028Z",
     "start_time": "2025-06-28T12:06:29.361530Z"
    }
   },
   "cell_type": "code",
   "source": "df[\"neg_log10PValue\"] = -np.log10(df[\"PValue\"])",
   "id": "36a65d962a1537fb",
   "outputs": [],
   "execution_count": 149
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-28T12:06:29.552362Z",
     "start_time": "2025-06-28T12:06:29.545953Z"
    }
   },
   "cell_type": "code",
   "source": "df",
   "id": "db94a6d6e351ad4d",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          Gene     logFC        PValue  logFC_abs Regular flag  \\\n",
       "0 0      Gene1 -5.444561  8.386036e-07   5.444561    down    F   \n",
       "  1      Gene2 -5.649062  1.463526e-06   5.649062    down    F   \n",
       "  2      Gene3 -5.934843  7.604120e-06   5.934843    down    F   \n",
       "  3      Gene4 -4.309913  8.621032e-06   4.309913    down    F   \n",
       "  4      Gene5 -4.166778  1.047254e-05   4.166778    down    F   \n",
       "...        ...       ...           ...        ...     ...  ...   \n",
       "2 186  Gene187  1.775998  4.395174e-02   1.775998      up    F   \n",
       "  194  Gene195  2.214651  4.693255e-02   2.214651      up    F   \n",
       "  198  Gene199  1.620426  4.781534e-02   1.620426      up    F   \n",
       "  199  Gene200  1.977304  4.815296e-02   1.977304      up    F   \n",
       "  201  Gene202  1.365912  4.856874e-02   1.365912      up    F   \n",
       "\n",
       "       neg_log10PValue  \n",
       "0 0           6.076443  \n",
       "  1           5.834600  \n",
       "  2           5.118951  \n",
       "  3           5.064441  \n",
       "  4           4.979948  \n",
       "...                ...  \n",
       "2 186         1.357024  \n",
       "  194         1.328526  \n",
       "  198         1.320433  \n",
       "  199         1.317377  \n",
       "  201         1.313643  \n",
       "\n",
       "[1669 rows x 7 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Gene</th>\n",
       "      <th>logFC</th>\n",
       "      <th>PValue</th>\n",
       "      <th>logFC_abs</th>\n",
       "      <th>Regular</th>\n",
       "      <th>flag</th>\n",
       "      <th>neg_log10PValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">0</th>\n",
       "      <th>0</th>\n",
       "      <td>Gene1</td>\n",
       "      <td>-5.444561</td>\n",
       "      <td>8.386036e-07</td>\n",
       "      <td>5.444561</td>\n",
       "      <td>down</td>\n",
       "      <td>F</td>\n",
       "      <td>6.076443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gene2</td>\n",
       "      <td>-5.649062</td>\n",
       "      <td>1.463526e-06</td>\n",
       "      <td>5.649062</td>\n",
       "      <td>down</td>\n",
       "      <td>F</td>\n",
       "      <td>5.834600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Gene3</td>\n",
       "      <td>-5.934843</td>\n",
       "      <td>7.604120e-06</td>\n",
       "      <td>5.934843</td>\n",
       "      <td>down</td>\n",
       "      <td>F</td>\n",
       "      <td>5.118951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gene4</td>\n",
       "      <td>-4.309913</td>\n",
       "      <td>8.621032e-06</td>\n",
       "      <td>4.309913</td>\n",
       "      <td>down</td>\n",
       "      <td>F</td>\n",
       "      <td>5.064441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gene5</td>\n",
       "      <td>-4.166778</td>\n",
       "      <td>1.047254e-05</td>\n",
       "      <td>4.166778</td>\n",
       "      <td>down</td>\n",
       "      <td>F</td>\n",
       "      <td>4.979948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2</th>\n",
       "      <th>186</th>\n",
       "      <td>Gene187</td>\n",
       "      <td>1.775998</td>\n",
       "      <td>4.395174e-02</td>\n",
       "      <td>1.775998</td>\n",
       "      <td>up</td>\n",
       "      <td>F</td>\n",
       "      <td>1.357024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>Gene195</td>\n",
       "      <td>2.214651</td>\n",
       "      <td>4.693255e-02</td>\n",
       "      <td>2.214651</td>\n",
       "      <td>up</td>\n",
       "      <td>F</td>\n",
       "      <td>1.328526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>Gene199</td>\n",
       "      <td>1.620426</td>\n",
       "      <td>4.781534e-02</td>\n",
       "      <td>1.620426</td>\n",
       "      <td>up</td>\n",
       "      <td>F</td>\n",
       "      <td>1.320433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>Gene200</td>\n",
       "      <td>1.977304</td>\n",
       "      <td>4.815296e-02</td>\n",
       "      <td>1.977304</td>\n",
       "      <td>up</td>\n",
       "      <td>F</td>\n",
       "      <td>1.317377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>Gene202</td>\n",
       "      <td>1.365912</td>\n",
       "      <td>4.856874e-02</td>\n",
       "      <td>1.365912</td>\n",
       "      <td>up</td>\n",
       "      <td>F</td>\n",
       "      <td>1.313643</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1669 rows × 7 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 150
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-28T12:06:29.731960Z",
     "start_time": "2025-06-28T12:06:29.721447Z"
    }
   },
   "cell_type": "code",
   "source": "df.to_csv(\"example.csv\", sep=\",\", header=True, index=False, encoding=\"utf-8\")",
   "id": "6f937c1ccea85048",
   "outputs": [],
   "execution_count": 151
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T07:12:15.725185Z",
     "start_time": "2025-06-29T07:12:15.515144Z"
    }
   },
   "cell_type": "code",
   "source": "df.to_excel(\"example.xlsx\", header=True, index=False, engine=\"openpyxl\")",
   "id": "dd9227abca6377fa",
   "outputs": [],
   "execution_count": 152
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "7fc6d96fab47d5e9"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
